package com.example.demo.mapper;

import com.example.demo.model.entity.achievement.ResearchPaper;
import com.example.demo.model.enums.PaperStatusEnum;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Mapper
public interface ResearchPaperMapper {

    /* 基础CRUD操作 */

    /**
     * 插入一篇新的论文
     * @param paper 论文实体
     */
    @Insert("INSERT INTO research_paper(paper_title, journal, issue, volume, page_range, " +
            "index_level, authors, first_author, corresponding_author, publish_date, " +
            "impact_factor, paper_link, submit_user_id, status, create_time, update_time) " +
            "VALUES(#{paperTitle}, #{journal}, #{issue}, #{volume}, #{pageRange}, " +
            "#{indexLevel}, #{authors}, #{firstAuthor}, #{correspondingAuthor}, #{publishDate}, " +
            "#{impactFactor}, #{paperLink}, #{submitUserId}, #{status}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(ResearchPaper paper);

    /**
     * 根据论文ID更新论文信息
     * @param paper 论文实体
     * @return 更新记录数
     */
    @Update("UPDATE research_paper SET paper_title = #{paperTitle}, journal = #{journal}, issue = #{issue}, volume = #{volume}, " +
            "page_range = #{pageRange}, index_level = #{indexLevel}, authors = #{authors}, first_author = #{firstAuthor}, " +
            "corresponding_author = #{correspondingAuthor}, publish_date = #{publishDate}, impact_factor = #{impactFactor}, " +
            "paper_link = #{paperLink}, submit_user_id = #{submitUserId}, status = #{status}, update_time = #{updateTime} " +
            "WHERE id = #{id}")
    int update(ResearchPaper paper);

    /**
     * 根据论文ID删除论文
     * @param id 论文ID
     * @return 删除记录数
     */
    @Delete("DELETE FROM research_paper WHERE id = #{id}")
    int delete(@Param("id") Long id);

    /**
     * 根据论文ID查询论文
     * @param id 论文ID
     * @return Optional包装的论文实体
     */
    @Select("SELECT * FROM research_paper WHERE id = #{id}")
    Optional<ResearchPaper> findById(@Param("id") Long id);

    /* 用户相关查询 */

    /**
     * 根据提交用户ID查询论文
     * @param submitUserId 用户ID
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE submit_user_id = #{submitUserId}")
    List<ResearchPaper> findBySubmitUserId(@Param("submitUserId") Long submitUserId);

    /**
     * 根据提交用户ID查询论文（按创建时间降序）
     * @param submitUserId 用户ID
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE submit_user_id = #{submitUserId} ORDER BY create_time DESC")
    List<ResearchPaper> findBySubmitUserIdOrderByCreateTimeDesc(Long submitUserId);

    /**
     * 根据论文ID和用户ID查询论文
     * @param paperId 论文ID
     * @param submitUserId 用户ID
     * @return Optional包装的论文实体
     */
    @Select("SELECT * FROM research_paper WHERE id = #{paperId} AND submit_user_id = #{submitUserId}")
    Optional<ResearchPaper> findByIdAndSubmitUserId(@Param("paperId") Long paperId, @Param("submitUserId") Long submitUserId);

    /* 状态相关查询 */

    /**
     * 根据状态查询论文
     * @param status 状态枚举
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE status = #{status.code}")
    List<ResearchPaper> findByStatus(@Param("status") PaperStatusEnum status);

    /**
     * 根据状态枚举查询论文（按创建时间升序）
     * @param status 状态枚举
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE status = #{status.code} ORDER BY create_time ASC")
    List<ResearchPaper> findByStatusOrderByCreateTimeAsc(PaperStatusEnum status);

    /**
     * 根据用户ID和状态查询论文
     * @param submitUserId 用户ID
     * @param status 状态枚举
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE submit_user_id = #{submitUserId} AND status = #{status.code}")
    List<ResearchPaper> findBySubmitUserIdAndStatus(@Param("submitUserId") Long submitUserId, @Param("status") PaperStatusEnum status);

    /**
     * 更新论文状态
     * @param paperId 论文ID
     * @param status 状态枚举
     * @return 更新记录数
     */
    @Update("UPDATE research_paper SET status = #{status.code} WHERE id = #{paperId}")
    int updatePaperStatus(@Param("paperId") Long paperId, @Param("status") PaperStatusEnum status);

    /* 分页查询 */

    /**
     * 分页查询所有论文
     * @param offset 偏移量
     * @param limit 每页数量
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper LIMIT #{offset}, #{limit}")
    List<ResearchPaper> findAll(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 根据用户ID分页查询论文
     * @param submitUserId 用户ID
     * @param offset 偏移量
     * @param limit 每页数量
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE submit_user_id = #{submitUserId} LIMIT #{offset}, #{limit}")
    List<ResearchPaper> findBySubmitUserIdPage(@Param("submitUserId") Long submitUserId,
                                               @Param("offset") int offset,
                                               @Param("limit") int limit);

    /**
     * 根据状态分页查询论文
     * @param status 状态枚举
     * @param offset 偏移量
     * @param limit 每页数量
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE status = #{status.code} LIMIT #{offset}, #{limit}")
    List<ResearchPaper> findByStatusPage(@Param("status") PaperStatusEnum status,
                                         @Param("offset") int offset,
                                         @Param("limit") int limit);

    /* 高级查询 */

    /**
     * 多条件搜索论文
     * @param title 标题关键词
     * @param journal 期刊关键词
     * @param author 作者关键词
     * @param status 状态枚举
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @param offset 偏移量
     * @param limit 每页数量
     * @return 论文列表
     */
    @Select("<script>" +
            "SELECT * FROM research_paper WHERE " +
            "<if test='title != null'>LOWER(paper_title) LIKE LOWER(CONCAT('%', #{title}, '%')) AND </if>" +
            "<if test='journal != null'>LOWER(journal) LIKE LOWER(CONCAT('%', #{journal}, '%')) AND </if>" +
            "<if test='author != null'>LOWER(first_author) LIKE LOWER(CONCAT('%', #{author}, '%')) AND </if>" +
            "<if test='status != null'>status = #{status.code} AND </if>" +
            "<if test='startDate != null'>publish_date &gt;= #{startDate} AND </if>" +
            "<if test='endDate != null'>publish_date &lt;= #{endDate} AND </if>" +
            "1=1 LIMIT #{offset}, #{limit}" +
            "</script>")
    List<ResearchPaper> searchPapers(@Param("title") String title,
                                     @Param("journal") String journal,
                                     @Param("author") String author,
                                     @Param("status") int status,
                                     @Param("startDate") LocalDate startDate,
                                     @Param("endDate") LocalDate endDate,
                                     @Param("offset") int offset,
                                     @Param("limit") int limit);
    /**
     * 根据用户ID列表查询论文
     * @param submitUserIds 用户ID列表（逗号分隔）
     * @param offset 偏移量
     * @param limit 每页数量
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE submit_user_id IN (${submitUserIds}) LIMIT #{offset}, #{limit}")
    List<ResearchPaper> findBySubmitUserIdIn(@Param("submitUserIds") String submitUserIds,
                                             @Param("offset") int offset,
                                             @Param("limit") int limit);

    /* 统计方法 */

    /**
     * 统计用户论文数量
     * @param userId 用户ID
     * @return 论文数量
     */
    @Select("SELECT COUNT(*) FROM research_paper WHERE submit_user_id = #{userId}")
    long countByUser(@Param("userId") Long userId);

    /**
     * 统计状态论文数量
     * @param status 状态枚举
     * @return 论文数量
     */
    @Select("SELECT COUNT(*) FROM research_paper WHERE status = #{status.code}")
    long countByStatus(@Param("status") PaperStatusEnum status);

    /**
     * 检查用户是否存在指定状态的论文
     * @param submitUserId 用户ID
     * @param statuses 状态列表（逗号分隔）
     * @return 是否存在
     */
    @Select("SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END " +
            "FROM research_paper WHERE submit_user_id = #{submitUserId} AND status IN (${statuses})")
    boolean existsBySubmitUserIdAndStatusIn(@Param("submitUserId") Long submitUserId,
                                            @Param("statuses") String statuses);

    /* 特殊条件查询 */

    /**
     * 根据期刊名称模糊查询（忽略大小写）
     * @param journal 期刊名称
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE LOWER(journal) LIKE LOWER(CONCAT('%', #{journal}, '%'))")
    List<ResearchPaper> findByJournalContainingIgnoreCase(@Param("journal") String journal);

    /**
     * 根据论文标题模糊查询（忽略大小写）
     * @param paperTitle 论文标题
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE LOWER(paper_title) LIKE LOWER(CONCAT('%', #{paperTitle}, '%'))")
    List<ResearchPaper> findByPaperTitleContainingIgnoreCase(@Param("paperTitle") String paperTitle);

    /**
     * 根据第一作者模糊查询（忽略大小写）
     * @param firstAuthor 第一作者
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE LOWER(first_author) LIKE LOWER(CONCAT('%', #{firstAuthor}, '%'))")
    List<ResearchPaper> findByFirstAuthorContainingIgnoreCase(@Param("firstAuthor") String firstAuthor);

    /**
     * 根据日期范围查询
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE publish_date BETWEEN #{startDate} AND #{endDate}")
    List<ResearchPaper> findByPublishDateBetween(@Param("startDate") LocalDate startDate,
                                                 @Param("endDate") LocalDate endDate);

    /**
     * 根据影响因子范围查询
     * @param min 最小值
     * @param max 最大值
     * @return 论文列表
     */
    @Select("SELECT * FROM research_paper WHERE impact_factor BETWEEN #{min} AND #{max}")
    List<ResearchPaper> findByImpactFactorBetween(@Param("min") BigDecimal min,
                                                  @Param("max") BigDecimal max);

    @Select({
            "<script>",
            "SELECT * FROM research_paper",
            "WHERE submit_user_id IN",
            "<foreach item='id' collection='submitUserIds' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "ORDER BY create_time DESC",
            "</script>"
    })
    List<ResearchPaper> findBySubmitUserIds(@Param("submitUserIds") List<Long> submitUserIds);
}